package it.ap.sa.acqua.dao.impl;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StopWatch;

import it.ap.sa.acqua.dao.beans.SearchFishFilter;
import it.ap.sa.acqua.dao.model.TFish;
import it.ap.sa.core.dao.util.AbstractGenericEntityDao;
import it.ap.sa.core.utils.ListUtils;
import it.ap.sa.core.utils.LogUtil;
/**
 * @author Alessandro Paolillo
 */
@Repository
public class TFishDao  extends AbstractGenericEntityDao<TFish>{

	/** logger **/
	private final Logger logger = LoggerFactory.getLogger(this.getClass());
	
	/**
	 * Il metodo recupera il pesce con id passato come input
	 * @param long id : id del pesce da recuperare
	 * @return TFish : pesce recuperato
	 * @throws Exception
	 */
	@Transactional
	public TFish findByPk(long id) throws Exception{
		try{
			String  hql     = "from " + TFish.class.getName() + " where id = :id";
			Session session = this.getSessionFactory().getCurrentSession();
			Query   query   = session.createQuery(hql);

			query.setLong("id", id);

			return (TFish)query.uniqueResult();
		}catch(Exception e){
			if(logger.isDebugEnabled())
				logger.debug("Error in findByPk", e);
			throw e;
		}
	}
	
	/**
	 * Il metodo si occupa del recupero di tutti i pesci dal db con id passati come parametro
	 * @param List<Long> ids : id dei pesci da recuperare
	 * @return List<TFish> : lista di pesci con id passati come parametri di ingresso
	 * @throws Exception
	 */
	public List<TFish> findByPks(List<Long> ids) throws Exception{
		try{
			String  hql     = "from " + TFish.class.getName() + " where id in (:ids)";
			Session session = this.getSessionFactory().getCurrentSession();
			Query   query   = session.createQuery(hql);

			query.setParameterList("ids", ids);

			return query.list();
		}catch(Exception e){
			if(logger.isDebugEnabled())
				logger.debug("Error in findByPks", e);
			throw e;
		}
	}
	
	/**
	 * Il metodo si occupa del recupero di tutti i pesci dal db
	 * @author Alessandro Paolillo
	 * @return List<TFish> : lista di tutti pesci
	 * @throws Exception
	 */
	public List<TFish> getFishs() throws Exception{
		try{
			String  hql     = "from " + TFish.class.getName();
			Session session = this.getSessionFactory().getCurrentSession();
			Query   query   = session.createQuery(hql);

			return query.list();
		}catch(Exception e){
			if(logger.isDebugEnabled())
				logger.debug("Error in getFishs", e);
			throw e;
		}
	}
	
	/**
	 * Il metodo si occupa del salvataggio di un pesce nel db
	 * @author Alessandro Paolillo
	 * @param {@link TFish} tFish : pesce da salvare nel db
	 * @return TFish :pesce salvato nel db
	 * @throws Exception
	 */
	@Transactional
	public TFish saveOrUpdate(TFish tFish) throws Exception {

		String    idLog     = "saveOrUpdate";
		StopWatch stopWatch = LogUtil.startStopWatch(idLog);
		logger.info(idLog);
		try {
			saveEntity(tFish);
		} catch (Exception ex) {

			logger.error("Si è verificato un errore in saveOrUpdate: " + ex.getMessage() , ex);
			throw(ex);
		} finally{

			logger.info(LogUtil.endStopWatch(stopWatch));
		}
		return tFish;
	}
	
	/**
	 * Query sulla tabella t_fish per il recupero dei pesci sulla base dei filtri passati con l'object SearchFishFilter
	 * @author Alessandro Paolillo
	 * @param {@link SearchFishFilter} filter : filtro per la ricerca dei pesci
	 * @return List<TFish> : lista dei pesci ricercati
	 * @throws Exception
	 */
	public List<TFish> searchFish( SearchFishFilter filter) throws Exception{
		
		Session session = this.getSessionFactory().getCurrentSession();
		try{
			
			/** Creazione della query **/
			String  hql     = addFishCriteriaQuery( filter );
			Query   query   = session.createQuery(hql);
			/** Imposto i parametri alla query **/
			query = addFishCriteria(query, filter);
			
			if ( filter!=null ) {
				/** Set paginator **/
				if ( filter.getiDisplayStart()!=-1 )
					query.setFirstResult( filter.getiDisplayStart() );
				if ( filter.getiDisplayLength()!=-1 )
					query.setMaxResults( filter.getiDisplayLength() );
			}
			/** Effettuo la query **/
			return (List<TFish>)query.list();
		}catch(Exception e){
			if(logger.isDebugEnabled())
				logger.debug("Error in searchFish", e);
			throw e;
		}
	}
	
	/**
	 * Query sulla tabella t_fish per il recupero del numero dei pesci sulla base dei filtri passati con l'object SearchFishFilter.
	 * Di solito viene effettuata con la query del metodo searchFish
	 * @author Alessandro Paolillo
	 * @param {@link SearchFishFilter} filter : filtro per la ricerca dei pesci
	 * @return Long : numero di pesci ricercati
	 * @throws Exception
	 */
	public Long searchCountFish( SearchFishFilter filter) throws Exception{
		
		Session session = this.getSessionFactory().getCurrentSession();
		try{
			/** Creazione della query **/
			String  hql     = addFishCriteriaQuery( filter );
			Query   query   = session.createQuery(hql);
			/** Imposto i parametri alla query **/
			query = addFishCriteria(query, filter);
			/** Effettuo la query **/
			List<TFish> list = (List<TFish>)query.list();
			if(ListUtils.listIsNotEmpty(list))
				return new Long ( list.size() );
			else 
				return new Long( 0 );
		}catch(Exception e){
			if(logger.isDebugEnabled())
				logger.debug("Error in searchCountFish", e);
			throw e;
		}
	}
	
	private String addFishCriteriaQuery (SearchFishFilter searchFishFilter) {
		
		String hql = "from " + TFish.class.getName()+" ";
		if (searchFishFilter!=null) {
			/** Set Base Filter **/	
			boolean isFirst = true;
			if ( searchFishFilter.getCommonName()!=null && !searchFishFilter.getCommonName().equals("") ) {
				hql += ( isFirst ? "WHERE" : "AND" ) +" common_name like :commonName ";
				isFirst = false;
			}
			if ( searchFishFilter.getScientificName()!=null && !searchFishFilter.getScientificName().equals("") ) {
				hql += ( isFirst ? "WHERE" : "AND" ) +" scientific_name like :scientificName ";
				isFirst = false;
			}
			if ( searchFishFilter.getOrigine()!=null && !searchFishFilter.getOrigine().equals("") ) {
				hql += ( isFirst ? "WHERE" : "AND" ) +" origin like :origin ";
				isFirst = false;
			}
			if ( searchFishFilter.getOrigineZone()!=null && !searchFishFilter.getOrigineZone().equals("") ) {
				hql += ( isFirst ? "WHERE" : "AND" ) +" origin_zone like :origineZone ";
				isFirst = false;
			}
			if ( searchFishFilter.getIdFamily()!=null ) {
				hql += ( isFirst ? "WHERE" : "AND" ) +" id_family = :idFamily ";
				isFirst = false;
			}
			/** Set Extend Filter **/
			if ( searchFishFilter.getCareLevel()!=null && !searchFishFilter.getCareLevel().equals("") ) {
				hql += ( isFirst ? "WHERE" : "AND" ) +" care_level = :careLevel ";
				isFirst = false;
			}
			if ( searchFishFilter.getWaterFlow()!=null && !searchFishFilter.getWaterFlow().equals("") ) {
				hql += ( isFirst ? "WHERE" : "AND" ) +" water_flow = :waterFlow ";
				isFirst = false;
			}
			if ( searchFishFilter.getTankLevel()!=null && !searchFishFilter.getTankLevel().equals("") ) {
				hql += ( isFirst ? "WHERE" : "AND" ) +" tank_level = :tankLevel ";
				isFirst = false;
			}
			if ( searchFishFilter.getTemperament()!=null && !searchFishFilter.getTemperament().equals("") ) {
				hql += ( isFirst ? "WHERE" : "AND" ) +" temperament = :temperament ";
				isFirst = false;
			}
			if ( searchFishFilter.getAcquariumType()!=null && !searchFishFilter.getAcquariumType().equals("") ) {
				hql += ( isFirst ? "WHERE" : "AND" ) +" acquarium_type = :acquariumType ";
				isFirst = false;
			}
			if ( searchFishFilter.getDiet()!=null && !searchFishFilter.getDiet().equals("") ) {
				hql += ( isFirst ? "WHERE" : "AND" ) +" diet = :diet ";
				isFirst = false;
			}	
			if ( searchFishFilter.getPhMax()!=null ) {
				hql += ( isFirst ? "WHERE" : "AND" ) +" ph_max <= :phMax ";
				isFirst = false;
			}
			if ( searchFishFilter.getPhMin()!=null ) {
				hql += ( isFirst ? "WHERE" : "AND" ) +" ph_min >= :phMin ";
				isFirst = false;
			}
			if ( searchFishFilter.getGhMax()!=null ) {
				hql += ( isFirst ? "WHERE" : "AND" ) +" gh_max <= :ghMax ";
				isFirst = false;
			}
			if ( searchFishFilter.getGhMin()!=null ) {
				hql += ( isFirst ? "WHERE" : "AND" ) +" gh_min >= :ghMin ";
				isFirst = false;
			}	
			if ( searchFishFilter.getTemperatureMax()!=null ) {
				hql += ( isFirst ? "WHERE" : "AND" ) +" temperature_max <= :temperatureMax ";
				isFirst = false;
			}
			if ( searchFishFilter.getTemperatureMin()!=null ) {
				hql += ( isFirst ? "WHERE" : "AND" ) +" temperature_min >= :temperatureMin ";
				isFirst = false;
			}
			
			if ( searchFishFilter.getExcludeFish()!=null ) {
				hql += ( isFirst ? "WHERE" : "AND" ) +" id not in ( :excludeFish ) ";
				isFirst = false;
			}	
		}
		return hql;
	}
	
	private Query addFishCriteria (Query query, SearchFishFilter searchFishFilter) {
		
		if (searchFishFilter!=null) {
			/** Set Base Parameter **/
			if ( searchFishFilter.getCommonName()!=null && !searchFishFilter.getCommonName().equals("") )
				query.setString("commonName", "%"+searchFishFilter.getCommonName()+"%");
			if ( searchFishFilter.getScientificName()!=null && !searchFishFilter.getScientificName().equals("") )
				query.setString("scientificName", "%"+searchFishFilter.getScientificName()+"%");
			if ( searchFishFilter.getOrigine()!=null && !searchFishFilter.getOrigine().equals("") )
				query.setString("origin", "%"+searchFishFilter.getOrigine()+"%");
			if ( searchFishFilter.getOrigineZone()!=null && !searchFishFilter.getOrigineZone().equals("") )
				query.setString("origineZone", "%"+searchFishFilter.getOrigineZone()+"%");
			if ( searchFishFilter.getIdFamily()!=null )
				query.setLong("idFamily", searchFishFilter.getIdFamily());
			/** Set Extend Parameter **/
			if ( searchFishFilter.getCareLevel()!=null && !searchFishFilter.getCareLevel().equals("") )
				query.setString("careLevel", searchFishFilter.getCareLevel());
			if ( searchFishFilter.getWaterFlow()!=null && !searchFishFilter.getWaterFlow().equals("") )
				query.setString("waterFlow", searchFishFilter.getWaterFlow());
			if ( searchFishFilter.getTankLevel()!=null && !searchFishFilter.getTankLevel().equals("") )
				query.setString("tankLevel", searchFishFilter.getTankLevel());
			if ( searchFishFilter.getTemperament()!=null && !searchFishFilter.getTemperament().equals("") )
				query.setString("temperament", searchFishFilter.getTemperament());
			if ( searchFishFilter.getAcquariumType()!=null && !searchFishFilter.getAcquariumType().equals("") )
				query.setString("acquariumType", searchFishFilter.getAcquariumType());
			if ( searchFishFilter.getDiet()!=null && !searchFishFilter.getDiet().equals("") )
				query.setString("diet", searchFishFilter.getDiet());
			
			if ( searchFishFilter.getPhMax()!=null )
				query.setDouble("phMax", searchFishFilter.getPhMax());
			if ( searchFishFilter.getPhMin()!=null )
				query.setDouble("phMin", searchFishFilter.getPhMin());
			if ( searchFishFilter.getGhMax()!=null )
				query.setDouble("ghMax", searchFishFilter.getGhMax());
			if ( searchFishFilter.getGhMin()!=null )
				query.setDouble("ghMin", searchFishFilter.getGhMin());
			if ( searchFishFilter.getTemperatureMax()!=null )
				query.setDouble("temperatureMax", searchFishFilter.getTemperatureMax());
			if ( searchFishFilter.getTemperatureMin()!=null )
				query.setDouble("temperatureMin", searchFishFilter.getTemperatureMin());
			
			if ( searchFishFilter.getExcludeFish()!=null )
				query.setParameterList("excludeFish", searchFishFilter.getExcludeFish());
		}
		return query;
	}
}